﻿using System;
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using DotNetCommon.Data;
using Microsoft.SqlServer.Types;
using DBUtil.SqlSegment;
using DBUtil.Provider.SqlServer.SqlSegment;
using System.Reflection;
using DotNetCommon.Extensions;
using System.Threading.Tasks;
using System.Threading;
using DotNetCommon;
using System.Linq;

namespace DBUtil.Provider.SqlServer;

/// <summary>
/// Microsoft SQL Server操作对象
/// </summary>
public partial class SqlServerAccess : DBAccess
{
    /// <inheritdoc />
    protected override DbConnection GetConnectionByConnectString() => new SqlConnection(DBConn);

    #region 一次最大插入行数
    public override int InsertRowsMaxCountPerBatch => base.InsertRowsMaxCountPerBatch > 0 ? base.InsertRowsMaxCountPerBatch : 1000;
    #endregion

    #region 创建DataAdapter
    /// <summary>
    /// 创建DataAdapter
    /// </summary>
    /// <returns></returns>
    protected override DataAdapter CreateAdapter(DbCommand cmd)
    {
        return new SqlDataAdapter(cmd as SqlCommand);
    }
    #endregion

    #region 创建参数 CreatePara
    /// <summary>
    /// 创建参数
    /// </summary>
    /// <returns></returns>
    public override DbParameter CreatePara()
    {
        return new SqlParameter();
    }
    #endregion

    #region 初始化
    private static Lazy<string> appName = new(() =>
    {
        var name = $"DBUtil-{Assembly.GetEntryAssembly().GetName().Name}";
        if (name.Length > 100) name = name.Substring(0, 100);
        return name;
    });
    /// <summary>
    /// 初始化
    /// </summary>
    /// <param name="DBConn">连接字符串</param>
    /// <param name="Settings">设置</param>
    public SqlServerAccess(string DBConn, DBSetting Settings) : base(EnumDBType.SqlServer, resetConnectionString(DBConn), Settings)
    {
        InitHooks();
    }

    /// <inheritdoc />
    public override string ParaPrefix => "@";

    private static string resetConnectionString(string connectionString)
    {
        var builder = new SqlConnectionStringBuilder();
        var defaultAppname = builder.ApplicationName;
        builder.ConnectionString = connectionString;
        if (defaultAppname == builder.ApplicationName)
        {
            builder.ApplicationName = appName.Value;
        }
        return builder.ConnectionString;
    }
    #endregion

    #region 获取当前db/schema/user的sqlseg
    public override string GetCurrentSchemaSqlSeg() => "SCHEMA_NAME()";
    public override string GetCurrentDataBaseSqlSeg() => "DB_NAME()";
    public override string GetCurrentUserSqlSeg(bool isPure = false) => "CURRENT_USER";
    public override string GetCurrentLoginUserSqlSeg(bool isPure = false) => "SYSTEM_USER";
    public override string GetCurrentDataBaseVersionSqlSeg() => "@@VERSION";
    #endregion

    #region 将任意对象转为可拼接的sql
    public override Result<string> ConvertToSqlSeg(object obj, object args = null)
    {
        //sqlserver 使用 N''形式
        if (obj is string str) return Result.Ok($"N{ProtectStringToSeg(str)}");
        var res = base.ConvertToSqlSeg(obj);
        if (res.Success) return res;

        //地理 几何
        if (obj is SqlGeography geography)
        {
            var grid = geography.STSrid;
            return Result.Ok($"geography::STGeomFromText('{geography}', {grid})");
        }
        if (obj is SqlGeometry geometry)
        {
            var grid = geometry.STSrid;
            return Result.Ok($"geometry::STGeomFromText('{geometry}', {grid})");
        }
        //层次结构
        if (obj is SqlHierarchyId hierarchyId)
        {
            return Result.Ok($"'{hierarchyId}'");
        }

        return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
    }
    #endregion

    #region GetSqlForPageSize
    /// <summary>获得分页的查询语句
    /// </summary>
    /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
    /// <param name="strOrder">排序字句如:order by id</param>
    /// <param name="PageSize">页面大小,如:10</param>
    /// <param name="PageIndex">页面索引从1开始,如:1</param>
    /// <returns>返回示例:select * from (select *,ROW_NUMBER() OVER(order by id) as RNO__ from (select id,name from person where age>10) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN (0*10+1) AND (1*10)</returns>
    public override string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex)
    {
        string sql = "";
        if (this.IsSqlServerVersion2012Compatible())
        {
            //使用fetch分页
            sql = $"{selectSql} {strOrder} offset {(PageIndex - 1) * PageSize} rows fetch next {PageSize} rows only";
        }
        else
        {
            //使用ROW_NUMBER分页
            sql = $"select * from (select *,ROW_NUMBER() OVER({strOrder}) as RNO__ from ({selectSql}) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN ({PageIndex - 1}*{PageSize}+1) AND ({PageIndex}*{PageSize})";
        }
        return sql;
    }
    #endregion

    #region Is系列: 判断表/视图/列/存储过程是否存在
    private string IsProcedureExistSql(string procPureName, string schemaPureName = null)
    {
        string sql = $"select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME={ProtectStringToSeg(procPureName)} and ROUTINE_TYPE='PROCEDURE'";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and ROUTINE_SCHEMA={ProtectStringToSeg(schemaPureName)}";
        else sql += $" and Db={GetCurrentSchemaSqlSeg()}";
        sql += $" and ROUTINE_CATALOG={GetCurrentDataBaseSqlSeg()}";
        return sql;
    }

    public override async Task<bool> IsProcedureExistAsync(string procPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(procPureName);
        var sql = IsProcedureExistSql(procPureName, schemaPureName);
        var r = await SelectScalarAsync<string>(sql, cancellationToken);
        return r.IsNotNullOrWhiteSpace();
    }

    private string IsTriggerExistSql(string triggerPureName, string schemaPureName = null)
    {
        string sql = $"select * from sys.objects where type='TR' and name={ProtectStringToSeg(triggerPureName)}";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and schema_id = {ProtectStringToSeg(schemaPureName)}";
        else sql += $" and schema_id={GetCurrentSchemaSqlSeg()}";
        sql += $" and parent_object_id={GetCurrentDataBaseSqlSeg()}";
        return sql;
    }
    public override async Task<bool> IsTriggerExistAsync(string triggerPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(triggerPureName);
        var sql = IsTriggerExistSql(triggerPureName, schemaPureName);
        return await SelectScalarAsync<int>(sql, cancellationToken) > 0;
    }
    #endregion

    #region 数据库管理对象 Manage
    private DBManage _dbmanage = null;
    /// <summary>
    /// 数据库管理对象
    /// </summary>
    public override DBManage Manage => _dbmanage ??= new SqlServerManage(this);
    #endregion

    #region 运算符
    protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new SqlServerDateTimeSqlSegment(this);
    protected override StringSqlSegment GetStringSqlSegment() => new SqlServerStringSqlSegment(this);
    protected override ConvertSqlSegment GetConvertSqlSegment() => new SqlServerConvertSqlSegment(this);
    protected override OtherSqlSegment GetOtherSqlSegment() => new SqlServerOtherSqlSegment(this);
    #endregion

    #region 使用SqlBulkCopy批量插入数据
    public override void BulkCopy(DataTable dt, string tableName = null, int timeoutSeconds = 60 * 30, int notifyAfter = 0, Func<long, bool> callBack = null)
    {
        if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
        if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
        SqlBulkCopy sbc = null;
        if (IsTransaction) sbc = new SqlBulkCopy((SqlConnection)CurrentConnection, SqlBulkCopyOptions.Default, (SqlTransaction)CurrentTransaction);
        else if (IsSession) sbc = new SqlBulkCopy((SqlConnection)CurrentConnection);
        else sbc = new SqlBulkCopy((SqlConnection)GetNewConnection());

        sbc.BulkCopyTimeout = timeoutSeconds;
        sbc.DestinationTableName = tableName;
        sbc.NotifyAfter = notifyAfter;
        if (callBack != null) sbc.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) =>
        {
            e.Abort = callBack(e.RowsCopied);
        };

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
        }
        sbc.WriteToServer(dt);
    }
    #endregion

    #region 获取刚插入的自增id的Sql语句
    public override string GetLastInsertedIdSeg(int len = 1)
    {
        return "scope_identity();";
    }
    #endregion

    #region 序列
    /// <summary>
    /// 是否支持序列, sqlsever 2012版本引入的序列
    /// </summary>
    /// <returns></returns>
    public override bool IsSupportSequence() => this.IsSqlServerVersion2012Compatible();

    /// <summary>
    /// 获取 下一个序列值 的sql语句
    /// </summary>
    /// <param name="name">序列名称</param>
    /// <returns></returns>
    public override string NextSequenceValueSeg(string name) => $"next value for {name}";
    #endregion

    #region PostDealEntityInfo
    protected override void PostDealEntityInfo(EntityInfo entityInfo)
    {
        var cols = entityInfo.Props;
        #region 处理 [SqlServerColumn]
        for (int i = 0; i < cols.Count; i++)
        {
            var entityPropertyInfo = cols[i];
            var propAttrs = entityPropertyInfo.PropertyInfo.GetCustomAttributes();
            //[SqlServerColumn]
            var colAttr = propAttrs.FirstOrDefault(i => i is SqlServerColumnAttribute) as SqlServerColumnAttribute;
            if (colAttr != null)
            {
                if (colAttr.Name.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.ColumnNamePure = colAttr.Name;
                }
                entityPropertyInfo.ColumnNameQuoted = AddQuote(entityPropertyInfo.ColumnNamePure);
                if (colAttr.TypeName.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.TypeName = colAttr.TypeName;
                }

                // [Column] 源码中默认就是-1
                entityPropertyInfo.Order = colAttr.Order;

                if (colAttr.TypeName.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.IsDbString = colAttr?.TypeName?.Contains("char", StringComparison.OrdinalIgnoreCase) == true || colAttr?.TypeName?.Contains("text", StringComparison.OrdinalIgnoreCase) == true;
                }
            }
        }
        #endregion
    }
    #endregion

    public override string ConvertJsonVariableToSql(string seg, EnumJsonDataType srcType, EnumJsonAcceptAsType destType)
    {
        if (destType == EnumJsonAcceptAsType.DataTableValue) throw new Exception($"不可以将sql语句转为DataTable中的值!");
        //null
        if (srcType == EnumJsonDataType.Null) return seg;
        else if (srcType == EnumJsonDataType.Number)
        {
            //number
            //return cast(1 as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return 1
            else return seg;
        }
        if (srcType == EnumJsonDataType.String)
        {
            //string
            //return '"tom"'
            if (destType == EnumJsonAcceptAsType.Doc) return $"json_quote({seg})";
            //return 'tom'
            else return seg;
        }
        else if (srcType == EnumJsonDataType.Bool)
        {
            //true/false
            //return cast(true as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return true
            else return seg;
        }
        else
        {
            //arr/obj
            //不用区分
            return seg;
        }
    }

    public override string GetDefaultDbType(Type type)
    {
        var colType = base.GetDefaultDbType(type);
        if (colType.IsNotNullOrEmpty()) return colType;
        if (type.IsNullable()) type = Nullable.GetUnderlyingType(type);
        var code = type.GetTypeCode();
        colType = "json";
        switch (code)
        {
            case TypeCode.SByte:
            case TypeCode.Byte:
            case TypeCode.UInt16:
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.UInt32:
                colType = "int";
                break;
            case TypeCode.Int64:
            case TypeCode.UInt64:
                colType = "bigint";
                break;
            case TypeCode.Double:
            case TypeCode.Single:
            case TypeCode.Decimal:
                colType = "decimal(30,15)";
                break;
            case TypeCode.DateTime:
                colType = "datetime(6)";
                break;
            case TypeCode.String:
                colType = "text";
                break;
            default:
                break;
        }
        return colType;
    }


    #region Is系列: 判断 表/视图/列/存储过程/触发器 是否存在
    /// <inheritdoc />
    protected override string IsColumnExistInTableOrViewSql(string tableOrViewPureName, string columnPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            return
                $"""
                select 1 from information_schema.columns t
                where t.table_catalog = {GetCurrentDataBaseSqlSeg()}
                    and t.table_schema = {ProtectStringToSeg(schemaPureName)}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                    and t.column_name={ProtectStringToSeg(columnPureName)}
                limit 1
                """;
        }
        else
        {
            return
                $"""
                select 1 from information_schema.columns t
                where t.table_catalog = {GetCurrentDataBaseSqlSeg()}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                    and t.column_name={ProtectStringToSeg(columnPureName)}
                limit 1
                """;
        }
    }
    /// <inheritdoc />
    protected override string IsTableOrViewExistSql(string tableOrViewPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            return
                $"""
                select 1 from information_schema.tables t 
                where t.table_catalog = {GetCurrentDataBaseSqlSeg()}
                    and t.table_schema = {ProtectStringToSeg(schemaPureName)}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                limit 1
                """;
        }
        else
        {
            return
                $"""
                select 1 from information_schema.tables t 
                where t.table_catalog = {GetCurrentDataBaseSqlSeg()}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                limit 1
                """;
        }
    }
    #endregion
}
